-- @owner: songjing20
-- @date: 2024-7-12
-- @testpoint: 验证start with connect by语句结合子查询及外部查询表的别名

--step1:创建查询表;expect:创建成功
drop table if exists t_connect_0050_01;
drop table if exists t_connect_0050_02;
create table t_connect_0050_01 (id int,pid int);
create table t_connect_0050_02 (id int,pid int);

--step2:查询表插入数据;expect:成功
insert into t_connect_0050_01 values(1,57),(2,69),(3,78),(4,65),(5,2),(6,7),(7,15),(8,63),(9,86),(10,52),(11,30),(12,55),(13,40),(14,38),
(15,95),(16,12),(17,64),(18,75),(19,65),(20,94),(21,56),(22,45),(23,23),(24,78),(25,35),(26,54),(27,49),(28,68),(29,0),(30,84),(31,55);
insert into t_connect_0050_01 values(32,64),(33,14),(34,11),(35,64),(36,90),(37,7),(38,65),(39,87),(40,5),(41,34),(42,86),(43,2),(44,56),
(45,18),(46,27),(47,23),(48,42),(49,34),(50,17),(51,32),(52,58),(53,74),(54,63),(55,77),(56,86),(57,19),(58,65),(59,47),(60,11);
insert into t_connect_0050_02 values(1,61),(2,7),(3,76),(4,21),(5,34),(6,7),(7,34),(8,68),(9,57),(10,28),(11,43),(12,11),(13,93),(14,19),
(15,57),(16,81),(17,32),(18,5),(19,94),(20,48),(21,99),(22,19),(23,40),(24,70),(25,9),(26,45),(27,87),(28,2),(29,11),(30,4),(31,11);
insert into t_connect_0050_02 values(32,96),(33,97),(34,16),(147,40),(36,57),(37,80),(38,19),(39,18),(40,37),(41,24),(42,76),(43,14),
(44,64),(45,48),(46,53),(47,75),(48,53),(49,15),(50,90),(51,64),(52,20),(53,91),(54,98),(55,91),(56,74),(57,88),(58,11),(59,71),(60,60);

--step3:where筛选条件exists子查询中包含connect by及上级查询表的别名;expect:查询成功
select t1.pid,t2.id
from t_connect_0050_01 t1 join t_connect_0050_02 t2
on t1.id = t2.pid
where exists(select id from t_connect_0050_02 start with id=10 connect by prior id=t1.id)
start with t1.id=40 connect by prior t2.id = t1.pid;

--step4:where筛选条件not exists子查询中包含connect by及上级查询表的别名;expect:查询成功
select t1.pid,t2.id
from t_connect_0050_01 t1 join t_connect_0050_02 t2
on t1.id = t2.pid
where not exists(select id from t_connect_0050_02 start with id=10 connect by prior id=t1.id)
start with t1.id=40 connect by prior t2.id = t1.pid;

--step5:where筛选条件exists子查询中包含connect by及但未包含上级查询表的别名;expect:查询成功
select t1.pid,t2.id
from t_connect_0050_01 t1 join t_connect_0050_02 t2
on t1.id = t2.pid
where exists(select id from t_connect_0050_02 start with id=10 connect by prior id=pid)
start with t1.id=40 connect by prior t2.id = t1.pid;

--step6:where筛选条件exists子查询中包含上级查询表的别名但未包含connect by;expect:查询成功
select t1.pid,t2.id
from t_connect_0050_01 t1 join t_connect_0050_02 t2
on t1.id = t2.pid
where exists(select id from t_connect_0050_02 where id=t1.id)
start with t1.id=40 connect by prior t2.id = t1.pid;

--step7:where筛选条件in子查询中包含connect by及上级查询表的别名;expect:查询成功
select t1.pid,t2.id
from t_connect_0050_01 t1 join t_connect_0050_02 t2
on t1.id = t2.pid
where t2.id in(select id from t_connect_0050_02 start with id=10 connect by prior id=t1.id)
start with t1.id=40 connect by prior t2.id = t1.pid;

--step8:where筛选条件not in子查询中包含connect by及上级查询表的别名;expect:查询成功
select t1.pid,t2.id
from t_connect_0050_01 t1 join t_connect_0050_02 t2
on t1.id = t2.pid
where t2.id not in(select id from t_connect_0050_02 start with id=10 connect by prior id=t1.id)
start with t1.id=40 connect by prior t2.id = t1.pid;

--step9:on连接条件子查询中包含connect by及上级查询表的别名;expect:查询成功
select t1.pid,t2.id from t_connect_0050_01 t1 join t_connect_0050_02 t2
on t1.id = (select id from t_connect_0050_02 start with id=10 connect by prior id=t1.pid)
start with t1.id=40 connect by prior t2.id = t1.pid;

--step10:清理环境;expect:成功
drop table if exists t_connect_0050_01;
drop table if exists t_connect_0050_02;
